package com.sc.highspeed.service;

import com.sc.highspeed.controller.result.JsonResult;
import com.sc.highspeed.db.model.CarInfoModel;
import com.sc.highspeed.db.model.EsCarInfoModel;
import com.sc.highspeed.db.model.EsCarInfoTempModel;
import com.sc.highspeed.temp.ErrorCarInfoModel;
import com.sc.highspeed.temp.ErrorEsCarInfoModel;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.FormulaParseException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import static com.sc.highspeed.constant.Constant.EXCEL_FORMAT_2007;
import static com.sc.highspeed.constant.Constant.EXCEL_FORMAT_2003;

/**
 * Created by Admin on 2017/12/8.
 */
@Service
public class ExcelHandleService {

    @Autowired
    private CarInfoService carInfoService;

    @Autowired
    private EsCarInfoService esCarInfoService;

    @Autowired
    private CarInfoTempService carInfoTempService;

    @Autowired
    private EsCarInfoTempService esCarInfoTempService;


    public List<JsonResult> getListByExcel(InputStream in, String fileName, String type) throws IOException {
        List<JsonResult> result = new ArrayList<JsonResult>();
        JsonResult jsonResult = new JsonResult();
        List<List<Object>> lists = new ArrayList<List<Object>>();
        Workbook wb = null;
        try {
            wb = getWorkbook(in, fileName);
        } catch (IOException e) {
            e.printStackTrace();
        }

        Sheet sheet = null;
        Row row = null;
        Cell cell = null;

        sheet = wb.getSheetAt(0);
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            List<Object> value = new ArrayList<Object>();
            for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
                cell = row.getCell(j);
                if (cell == null) {
                    cell = new Cell() {
                        public int getColumnIndex() {
                            return 0;
                        }

                        public int getRowIndex() {
                            return 0;
                        }

                        public Sheet getSheet() {
                            return null;
                        }

                        public Row getRow() {
                            return null;
                        }

                        public void setCellType(int i) {

                        }

                        public int getCellType() {
                            return 0;
                        }

                        public int getCachedFormulaResultType() {
                            return 0;
                        }

                        public void setCellValue(double v) {

                        }

                        public void setCellValue(Date date) {

                        }

                        public void setCellValue(Calendar calendar) {

                        }

                        public void setCellValue(RichTextString richTextString) {

                        }

                        public void setCellValue(String s) {

                        }

                        public void setCellFormula(String s) throws FormulaParseException {

                        }

                        public String getCellFormula() {
                            return null;
                        }

                        public double getNumericCellValue() {
                            return 0;
                        }

                        public Date getDateCellValue() {
                            return null;
                        }

                        public RichTextString getRichStringCellValue() {
                            return null;
                        }

                        public String getStringCellValue() {
                            return null;
                        }

                        public void setCellValue(boolean b) {

                        }

                        public void setCellErrorValue(byte b) {

                        }

                        public boolean getBooleanCellValue() {
                            return false;
                        }

                        public byte getErrorCellValue() {
                            return 0;
                        }

                        public void setCellStyle(CellStyle cellStyle) {

                        }

                        public CellStyle getCellStyle() {
                            return null;
                        }

                        public void setAsActiveCell() {

                        }

                        public void setCellComment(Comment comment) {

                        }

                        public Comment getCellComment() {
                            return null;
                        }

                        public void removeCellComment() {

                        }

                        public Hyperlink getHyperlink() {
                            return null;
                        }

                        public void setHyperlink(Hyperlink hyperlink) {

                        }

                        public void removeHyperlink() {

                        }

                        public CellRangeAddress getArrayFormulaRange() {
                            return null;
                        }

                        public boolean isPartOfArrayFormulaGroup() {
                            return false;
                        }
                    };
                }
                value.add(getCellValue(cell));
            }

            lists.add(value);
        }

        in.close();
        wb.close();

        result = modelHandle(setModel(lists,type), type);

        return result;

    }


    public Workbook getWorkbook(InputStream in, String fileName) throws IOException {
        Workbook wb = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if (fileType.equals(EXCEL_FORMAT_2003)) {
            wb = new HSSFWorkbook(in);
        } else if (fileType.equals(EXCEL_FORMAT_2007)) {
            wb = new XSSFWorkbook(in);
        }

        return wb;
    }


    public Object getCellValue(Cell cell) {
        Object value = null;
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        DecimalFormat numberFormat = new DecimalFormat("0");
        if (cell.getCellStyle() != null) {
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    value = cell.getRichStringCellValue().getString();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if (cell.getCellStyle().getDataFormatString().equals("m/d/yy h:mm")) {
                        value = dateFormat.format(cell.getDateCellValue());
                    } else {
                        value = numberFormat.format(cell.getNumericCellValue());
                    }
                    break;
                default:
                    break;
            }
        } else {
            value = cell.getStringCellValue();
        }

        return value;
    }


    public List<Object> setModel(List<List<Object>> lists, String type) {
        List<Object> objects = new ArrayList<Object>();
        if (type.equals("carInfo")) {
            for (int i = 0; i < lists.size(); i++) {
                List<Object> list = lists.get(i);
                CarInfoModel carInfoModel = new CarInfoModel();
                ErrorCarInfoModel errorCarInfoModel = new ErrorCarInfoModel();
//                try {
                    carInfoModel.setColor(String.valueOf(list.get(0)));
                    carInfoModel.setNumber(String.valueOf(list.get(1)));
                    carInfoModel.setOwner(String.valueOf(list.get(2)));
                    carInfoModel.setBrand(String.valueOf(list.get(3)));
                    carInfoModel.setModel(String.valueOf(list.get(4)));
                    carInfoModel.setType(String.valueOf(list.get(5)));
                    carInfoModel.setProperty(String.valueOf(list.get(6)));
                    carInfoModel.setAxleNum(Integer.parseInt(String.valueOf(list.get((7))) == "null" ? null : String.valueOf(list.get((7)))));
                    carInfoModel.setMaxPassenger(Integer.parseInt(String.valueOf(list.get((8))) == "null" ? null : String.valueOf(list.get((8)))));
                    carInfoModel.setMaxLoad(Integer.parseInt(String.valueOf(list.get((9))) == "null" ? null : String.valueOf(list.get((9)))));
                    carInfoModel.setWeight((Integer.parseInt(String.valueOf(list.get((10))) == "null" ? null : String.valueOf(list.get((10))))));
                    carInfoModel.setCreator(String.valueOf(list.get(11)));
                    carInfoModel.setCompany(String.valueOf(list.get((12))));
                    carInfoModel.setTime(String.valueOf(list.get(13)));
                    carInfoModel.setReviewer(String.valueOf(list.get(14)));
                    carInfoModel.setAudit(String.valueOf(list.get(15)));
                    carInfoModel.setAuditTime(String.valueOf(list.get(16)));
                    carInfoModel.setRemark(String.valueOf(list.get(17)));
                    carInfoModel.setCarLevel(String.valueOf(list.get(18)));
//                } catch (Exception e) {
//                    errorCarInfoModel.setColor(String.valueOf(list.get(0)));
//                    errorCarInfoModel.setNumber(String.valueOf(list.get(1)));
//                    errorCarInfoModel.setOwner(String.valueOf(list.get(2)));
//                    errorCarInfoModel.setBrand(String.valueOf(list.get(3)));
//                    errorCarInfoModel.setModel(String.valueOf(list.get(4)));
//                    errorCarInfoModel.setType(String.valueOf(list.get(5)));
//                    errorCarInfoModel.setProperty(String.valueOf(list.get(6)));
//                    errorCarInfoModel.setAxleNum(String.valueOf(list.get((7))));
//                    errorCarInfoModel.setMaxPassenger(String.valueOf(list.get((8))));
//                    errorCarInfoModel.setMaxLoad(String.valueOf(list.get((9))));
//                    errorCarInfoModel.setWeight(String.valueOf(list.get((10))));
//                    errorCarInfoModel.setCreator(String.valueOf(list.get(11)));
//                    errorCarInfoModel.setCompany(String.valueOf(list.get((12))));
//                    errorCarInfoModel.setTime(String.valueOf(list.get(13)));
//                    errorCarInfoModel.setReviewer(String.valueOf(list.get(14)));
//                    errorCarInfoModel.setAudit(String.valueOf(list.get(15)));
//                    errorCarInfoModel.setAuditTime(String.valueOf(list.get(16)));
//                    errorCarInfoModel.setRemark(String.valueOf(list.get(17)));
//                    errorCarInfoModel.setCarLevel(String.valueOf(list.get(18)));
//                    objects.add(errorCarInfoModel);
//                    continue;
//                }
                carInfoModel.setStatus("添加");
                objects.add(carInfoModel);

            }
        } else if (type.equals("esCarInfo")) {
            for (int i = 0; i < lists.size(); i++) {
                List<Object> list = lists.get(i);
                EsCarInfoModel esCarInfoModel = new EsCarInfoModel();
                ErrorEsCarInfoModel errorEsCarInfoModel = new ErrorEsCarInfoModel();
                try {
                    esCarInfoModel.setColor(String.valueOf(list.get(0)));
                    esCarInfoModel.setNumber(String.valueOf(list.get(1)));
                    esCarInfoModel.setHandleTime(String.valueOf(list.get(2)));
                    esCarInfoModel.setType(String.valueOf(list.get(3)));
                    esCarInfoModel.setRmb(Integer.parseInt(String.valueOf(list.get((4))) == "null" ? null : String.valueOf(list.get((4)))));
                    esCarInfoModel.setCreator(String.valueOf(list.get(5)));
                    esCarInfoModel.setCompany(String.valueOf(list.get(6)));
                    esCarInfoModel.setInputTime(String.valueOf(list.get(7)));
                    esCarInfoModel.setReviewer(String.valueOf(list.get(8)));
                    esCarInfoModel.setAudit(String.valueOf(list.get(9)));
                    esCarInfoModel.setAuditTime(String.valueOf(list.get(10)));
                    esCarInfoModel.setIsList(String.valueOf(list.get(11)));
                    esCarInfoModel.setList(String.valueOf(list.get(12)));
                    esCarInfoModel.setIsProcess(String.valueOf(list.get(13)));
                    esCarInfoModel.setProcess(String.valueOf(list.get(14)));
                    esCarInfoModel.setRemark(String.valueOf(list.get(15)));
                    esCarInfoModel.setIsHandle(String.valueOf(list.get(16)));
                } catch (Exception e) {
                    errorEsCarInfoModel.setColor(String.valueOf(list.get(0)));
                    errorEsCarInfoModel.setNumber(String.valueOf(list.get(0)));
                    errorEsCarInfoModel.setHandleTime(String.valueOf(list.get(0)));
                    errorEsCarInfoModel.setType(String.valueOf(list.get(0)));
                    errorEsCarInfoModel.setRmb(String.valueOf(list.get(0)));
                    errorEsCarInfoModel.setCreator(String.valueOf(list.get(0)));
                    errorEsCarInfoModel.setCompany(String.valueOf(list.get(0)));
                    errorEsCarInfoModel.setInputTime(String.valueOf(list.get(0)));
                    errorEsCarInfoModel.setReviewer(String.valueOf(list.get(0)));
                    errorEsCarInfoModel.setAudit(String.valueOf(list.get(0)));
                    errorEsCarInfoModel.setAuditTime(String.valueOf(list.get(0)));
                    errorEsCarInfoModel.setIsList(String.valueOf(list.get(0)));
                    errorEsCarInfoModel.setList(String.valueOf(list.get(0)));
                    errorEsCarInfoModel.setIsProcess(String.valueOf(list.get(0)));
                    errorEsCarInfoModel.setProcess(String.valueOf(list.get(0)));
                    errorEsCarInfoModel.setRemark(String.valueOf(list.get(0)));
                    errorEsCarInfoModel.setIsHandle(String.valueOf(list.get(0)));
                    objects.add(errorEsCarInfoModel);
                    continue;
                }

                esCarInfoModel.setStatus("添加");
                objects.add(esCarInfoModel);
            }
        } else if (type.equals("user")) {
            for (int i = 0; i < lists.size(); i++) {
                List<Object> list = lists.get(i);

            }
        }

        return objects;
    }

    public List<JsonResult> modelHandle(List<Object> objects, String type) {
        List<JsonResult> results = new ArrayList<JsonResult>();
        List<JsonResult> success = new ArrayList<JsonResult>();
        List<JsonResult> fail = new ArrayList<JsonResult>();

        CarInfoModel carInfoModel = null;
        EsCarInfoModel esCarInfoModel = null;

        for (int i = 0; i < objects.size(); i++) {
            JsonResult result = new JsonResult();
            if (objects.get(i) instanceof ErrorCarInfoModel || objects.get(i) instanceof ErrorEsCarInfoModel) {
                fail.add(result.setCode(0).setStatus(false).setMsg("上传失败！请检查数据格式！").setObject(objects.get(i)));
                continue;
            }
            if (type.equals("carInfo")) {
                carInfoModel = (CarInfoModel)objects.get(i);
                if (carInfoService.getCarInfoByNumber(carInfoModel.getNumber(), carInfoModel.getColor()) != null) {
                    carInfoModel.setId(carInfoService.getCarInfoByNumber(carInfoModel.getNumber(), carInfoModel.getColor()).getId());
                    int code = carInfoService.updateCarInfoById(carInfoModel);
                    if (code == 1) {
                        result.setCode(1);
                        result.setStatus(true);
                        result.setObject(carInfoModel);
                        success.add(result);
                    } else {
                        result.setCode(0);
                        result.setStatus(false);
                        result.setMsg("数据覆盖失败！");
                        result.setObject(carInfoModel);
                        fail.add(result);
                    }
                } else {
                    int code = carInfoService.insertCarInfo(carInfoModel);
                    if (code == 1) {
                        result.setCode(1);
                        result.setStatus(true);
                        result.setObject(carInfoModel);
                        success.add(result);
                    } else {
                        result.setCode(0);
                        result.setStatus(false);
                        result.setMsg("数据插入失败！");
                        result.setObject(carInfoModel);
                        fail.add(result);
                    }
                }

            } else if (type.equals("esCarInfo")) {
                esCarInfoModel = (EsCarInfoModel) objects.get(i);
                int code = esCarInfoService.insertEsCarInfo(esCarInfoModel);
                if (code == 1) {
                    result.setCode(1);
                    result.setStatus(true);
                    result.setObject(esCarInfoModel);
                    success.add(result);
                } else {
                    result.setCode(0);
                    result.setStatus(false);
                    result.setMsg("数据插入失败！");
                    result.setObject(esCarInfoModel);
                    fail.add(result);
                }

            }
        }
        results.add(new JsonResult().setCode(1).setStatus(true).setMsg("上传成功").setObject(success));
        results.add(new JsonResult().setCode(0).setStatus(false).setMsg("上传失败").setObject(fail));

        return results;
    }


}
